import random

from flask import (Flask, render_template,
				   request, Response, json, redirect, url_for)

from fenxi.work import Index_charts
import pymysql
import pandas as pd
from spider.runSpider import runSpider
app = Flask(__name__)

conn = pymysql.Connection(host="localhost", port=3306, user="root", password="123456", database="test1")
df = pd.read_sql_query(
	"select d.rent,d.squares,c.district,c.town,c.province,d.label from city c,details d where d.citycode=c.citycode",
	conn)


@app.route("/")
def index():
	return render_template("index.html")


@app.route("/admin")
def run_spider():
	return render_template("spider_admin.html")


@app.route("/ran")
def bar_random():
	# conn = pymysql.Connection(host="localhost", port=3306, user="root", password="123456", database="lianjia")
	# df = pd.read_sql_query("select d.rent,d.squares,c.district,c.town,c.province,d.label from city c,details d where d.citycode=c.citycode",conn)
	title, money, area = Index_charts(df).bar()
	print(title)
	data = {"barData": [i for i in money],
			"lineData": [i for i in area],
			"title": [i for i in title]}
	return Response(json.dumps(data), content_type='application/json', status=200)


@app.route("/pie/random")
def pie_random():
	# conn = pymysql.Connection(host="localhost", port=3306, user="root", password="123456", database="lianjia")
	# df = pd.read_sql_query(
	# 	"select d.rent,d.squares,c.district,c.town,c.province,d.label from city c,details d where d.citycode=c.citycode",
	# 	conn)

	name, value = Index_charts(df).pie()
	data = {"dataPie": [{"value": value[i], 'name': name[i]} for i in range(len(name))], "title": name}
	return Response(json.dumps(data), content_type="application/json", status=200)


@app.route("/index/map")
def map_random():
	# print(index().map())
	# conn = pymysql.Connection(host="localhost", port=3306, user="root", password="123456", database="lianjia")
	# df = pd.read_sql_query(
	# 	"select d.rent,d.squares,c.district,c.town,c.province,d.label from city c,details d where d.citycode=c.citycode",
	# 	conn)

	name, value = Index_charts(df).map()
	# print("name:", name, "value:", value)
	data = {"mapData": [{"name": name[i], "value": value[i]} for i in range(len(name))]}
	# print("map:", data)
	return Response(json.dumps(data), content_type="application/json", status=200)


@app.route("/work")
def work_random():
	# s =
	# conn = pymysql.Connection(host="localhost", port=3306, user="root", password="123456", database="lianjia")
	# df = pd.read_sql_query(
	# 	"select d.rent,d.squares,c.district,c.town,c.province,d.label from city c,details d where d.citycode=c.citycode",
	# 	conn)

	name, value = Index_charts(df).work()

	# print("work:", name, value)
	# print("name:", name, "value:", value)
	data = {"workData": [{"name": name[i], "value": value[i]} for i in range(len(name))]}
	return Response(json.dumps(data), content_type="application/json", status=200)


@app.route("/index/zhibiao")
def zhibo():
	# conn = pymysql.Connection(host="localhost", port=3306, user="root", password="123456", database="lianjia")
	# df = pd.read_sql_query(
	# 	"select d.rent,d.squares,c.district,c.town,c.province,d.label from city c,details d where d.citycode=c.citycode",
	# 	conn)

	data = [{"value": random.randint(1, 100), "name": "增长量"}]
	return Response(json.dumps(data), content_type="application/json", status=200)


@app.route("/right/bar")
def right():
	# conn = pymysql.Connection(host="localhost", port=3306, user="root", password="123456", database="lianjia")
	# df = pd.read_sql_query(
	# 	"select d.rent,d.squares,c.district,c.town,c.province,d.label from city c,details d where d.citycode=c.citycode",
	# 	conn)

	ti, da = Index_charts(df).rigth()
	data = {"title": ti, "data": da}
	return Response(json.dumps(data), content_type="application/json", status=200)


@app.route("/bili")
def bili():
	# conn = pymysql.Connection(host="localhost", port=3306, user="root", password="123456", database="lianjia")
	# df = pd.read_sql_query(
	# 	"select d.rent,d.squares,c.district,c.town,c.province,d.label from city c,details d where d.citycode=c.citycode",
	# 	conn)

	bili, count = Index_charts(df).shuliang()
	# print(bili, count)
	data = {"bili": [bili], "count": [count]}
	return Response(json.dumps(data), content_type="application/json", status=200)


@app.route("/left/work")
def left_work():
	# conn = pymysql.Connection(host="localhost", port=3306, user="root", password="123456", database="lianjia")
	# df = pd.read_sql_query(
	# 	"select d.rent,d.squares,c.district,c.town,c.province,d.label from city c,details d where d.citycode=c.citycode",
	# 	conn)

	name, value = Index_charts(df).left_work()
	# print("work:", name, value)
	# print("name:", name, "value:", value)
	data = {"workData": [{"name": name[i], "value": value[i]} for i in range(len(name))]}
	return Response(json.dumps(data), content_type="application/json", status=200)


@app.route("/spider", methods=["POST"])
def spider():
	r = runSpider()
	urls = request.form.listvalues()

	for url in urls:
		print("flask:", url)
		r.startSpider(url)
	print(f"成功关闭{r.close()}")
	return redirect(location="http://127.1.1.1:6800/jobs")  # 重定向到Scrapyd API地址中


if __name__ == "__main__":
	app.run()
